﻿-- Function: public.timeserial_treedata(integer, integer, integer)

---DROP FUNCTION public.timeserial_treedata(integer, integer, integer);

CREATE OR REPLACE FUNCTION public.pushbill(
    p_sourcebill varchar,---来源表名
    p_sourcebillid integer,----来源表ID
    p_bill varchar,---表名
    p_userid integer,---用户ID
    p_typeid integer-----合同有来源单据类型0续签，1另签，2补签
    )
  RETURNS json  AS
$BODY$
declare  
    v_billid integer;
    v_sql text;
    v_data text;
    v_cnt integer;
begin
  if p_sourcebill ='contractapply' then
     if p_bill='contracts' then
	 select nextval('public.contracts_id_seq') into v_billid;
	 
	 insert into public.contracts (id,userid ,bill_date,employeeid ,departmentid,code ,
	  name,type,party_aid,party_bid,party_cid,pay_direction,pricetype,
	  sign_date, start_date,end_date,amount,amount_words,
	  message,sourcetype,sourceid,typeid,org_id,updated_at,created_at,status) 
	 select v_billid,p_userid ,now(),employeeid ,departmentid,public.gaincode('contracts',0,'code',now(),org_id,p_userid) ,
	  name,type,party_aid,party_bid,party_cid,pay_direction,pricetype,
	  sign_date, start_date,end_date,amount,amount_words,
	  message,'contractapply',id,p_typeid,org_id,now(),now(),0
	  from  public.contractapply where id=p_sourcebillid;

	   insert into public.contractactivity(userid,contractsid,message,updated_at,
	   created_at,sourcetype ,sourceid)
	   select p_userid,v_billid,'合同申请',bill_date,
	   bill_date,'contractapply' ,id from public.contractapply where id=p_sourcebillid;

	   insert into public.contractactivity(userid,contractsid,message,updated_at,
	   created_at,sourcetype ,sourceid)
	   select p_userid,v_billid,'合同新建',now(),
	   now(),'contracts' ,id from public.contracts where id=v_billid;

	   insert into public.attaches(userid,type,annex,updated_at,created_at,
           sourcetype,sourceid,org_id)
           select p_userid,p_bill,'[]'::json,now(),now(),
           p_bill,v_billid,org_id from  public.contracts where id=v_billid;

          select array_to_json(array_agg(row_to_json(t))) into v_data from (select 0 as flag,id,code from public.contracts where id=v_billid) t;
         return v_data::json; 
     end if;
  elsif p_sourcebill ='contracts' then
     if p_bill='contracts' then
	 select nextval('public.contracts_id_seq') into v_billid;
	 insert into public.contracts (id,userid ,bill_date,employeeid ,departmentid,code ,
	  name,type,party_aid,party_bid,party_cid,pay_direction,pricetype,
	  sign_date, start_date,end_date,amount,amount_words,
	  message,sourcetype,sourceid,
	  officialcode,templateid,templatecontent,typeid,org_id,updated_at,created_at,status,contracttype,amounttype) 
	 select v_billid,p_userid ,now(),employeeid ,departmentid,public.gaincode('contracts',0,'code',now(),org_id,p_userid),
	  name,type,party_aid,party_bid,party_cid,pay_direction,pricetype,
	  sign_date, start_date,end_date,amount,amount_words,
	  message,'contracts',id,
	  '',templateid,templatecontent,p_typeid,org_id,now(),now(),0,contracttype,amounttype
	  from  public.contracts where id=p_sourcebillid;

	   insert into public.contractactivity(userid,contractsid,message,updated_at,
	   created_at,sourcetype ,sourceid)
	   select p_userid,p_sourcebillid,
	   case when p_typeid=0 then '合同被续签' when p_typeid=1 then '合同被另签' else '合同被补签' end,now(),
	   now(),'contracts' ,id from public.contracts where id=p_sourcebillid;

	   insert into public.contractactivity(userid,contractsid,message,updated_at,
	   created_at,sourcetype ,sourceid)
	   select p_userid,v_billid,
	   case when p_typeid=0 then '合同续签' when p_typeid=1 then '合同另签' else '合同补签' end,now(),
	   now(),'contracts' ,id from public.contracts where id=v_billid;

	   insert into public.attaches(userid,type,annex,updated_at,created_at,
           sourcetype,sourceid,org_id)
           select p_userid,p_bill,'[]'::json,now(),now(),
           p_bill,v_billid,org_id from  public.contracts where id=v_billid;

           select array_to_json(array_agg(row_to_json(t))) into v_data from (select 0 as flag,id,code from public.contracts where id=v_billid) t;
           return v_data::json; 
     elsif p_bill='contractseal' then---合同用印申请单
       select count(*) into v_cnt from public.contractseal where contractsid=p_sourcebillid;
       if v_cnt=0 then
	 select nextval('public.contractseal_id_seq') into v_billid;
	 insert into public.contractseal(id,userid,bill_date,contractsid,code, name,message,
	  sourcetype,sourceid,org_id,updated_at,created_at,departmentid,sealdate,employeeid) 
	 select v_billid,p_userid ,now(),id ,public.gaincode('contractseal',0,'code',now(),org_id,p_userid),name,'',
	  'contracts',id,org_id,now(),now(),departmentid,now(),p_userid  from  public.contracts where id=p_sourcebillid;

	   insert into public.contractactivity(userid,contractsid,message,updated_at,
	   created_at,sourcetype ,sourceid)
	   select p_userid,p_sourcebillid,'合同用印',now(),
	   now(),p_bill ,v_billid from public.contractseal where id=v_billid;
	   
          select array_to_json(array_agg(row_to_json(t))) into v_data from (select 0 as flag,id,code from public.contractseal where id=v_billid) t;
          return v_data::json;  
        else
          select array_to_json(array_agg(row_to_json(t))) into v_data from 
          (select 1 as flag, id,'合同用印申请单已存在:' || code as code from public.contractseal where contractsid=p_sourcebillid) t;
          return v_data::json;  
        end if; 
    elsif p_bill='contractrecord' then---合同备案申请单
       select count(*) into v_cnt from public.contractrecord where contractsid=p_sourcebillid;
       if v_cnt=0 then
	 select nextval('public.contractrecord_id_seq') into v_billid;
	 insert into public.contractrecord(id,userid,bill_date,contractsid,code, name,message,
	  sourcetype,sourceid,org_id,updated_at,created_at,departmentid) 
	 select v_billid,p_userid ,now(),id ,public.gaincode('contractrecord',0,'code',now(),org_id,p_userid) ,name,'',
	  'contracts',id,org_id,now(),now(),departmentid  from  public.contracts where id=p_sourcebillid;

	   insert into public.contractactivity(userid,contractsid,message,updated_at,
	   created_at,sourcetype ,sourceid)
	   select p_userid,p_sourcebillid,'合同备案',now(),
	   now(),p_bill ,v_billid from public.contractrecord where id=v_billid;
	   
          select array_to_json(array_agg(row_to_json(t))) into v_data from (select 0 as flag,id,code from public.contractrecord where id=v_billid) t;
          return v_data::json;  
        else
          select array_to_json(array_agg(row_to_json(t))) into v_data from 
          (select 1 as flag, id,'合同备案申请单已存在:' || code as code from public.contractrecord where contractsid=p_sourcebillid) t;
          return v_data::json;  
        end if; 
    elsif p_bill='contractchange' then---合同变更申请单
	 select nextval('public.contractchange_id_seq') into v_billid;
	 insert into public.contractchange(id,userid,bill_date,contractsid,employeeid,departmentid,code,contractcode,
    name,type,party_aid,party_bid,party_cid,pay_direction,pricetype,sign_date,
    start_date,end_date,amount,amount_words,budgetcode,officialcode,templateid,
    templatecontent,message,status,changebefore,updated_at,created_at,sourcetype,
    sourceid,org_id,contractsealid,contractrecordid,billstatusid,typeid,workflow_state,
    amounttype,infrastructure,recordcode,puremployeeid,warn_date,contracttype,
    zhengcai,isbulu,warn_message,warn_check,volumepurchase,warn_checkdate,
    iskeyan,iszhaobiao,empowersex,empowerduties,agreementcount,empowerperson,
    warnsealuser) 
	select v_billid,p_userid,now(),id,employeeid,departmentid,public.gaincode('contractchange',0,'code',now(),org_id,p_userid),
	code,name,type,party_aid,party_bid,party_cid,pay_direction,pricetype,sign_date,
    start_date,end_date,amount,amount_words,budgetcode,officialcode,templateid,
    templatecontent,message,status,
	(select array_to_json(array_agg(row_to_json(t)))  from (select* from  public.contracts where id=p_sourcebillid) t),
	now(),now(),'contracts',
    id,org_id,contractsealid,contractrecordid,billstatusid,typeid,workflow_state,
    amounttype,infrastructure,recordcode,puremployeeid,warn_date,contracttype,
    zhengcai,isbulu,warn_message,warn_check,volumepurchase,warn_checkdate,
    iskeyan,iszhaobiao,empowersex,empowerduties,agreementcount,empowerperson,
    warnsealuser
	  from  public.contracts where id=p_sourcebillid;

	   insert into public.contractactivity(userid,contractsid,message,updated_at,
	   created_at,sourcetype ,sourceid)
	   select p_userid,p_sourcebillid,'合同变更',now(),
	   now(),p_bill ,v_billid from public.contractchange where id=v_billid;
	    
           select array_to_json(array_agg(row_to_json(t))) into v_data from (select 0 as flag,id,code from public.contractchange where id=v_billid) t;
           return v_data::json;
    elsif p_bill='contractprogress' then---合同违约登记单
	 select nextval('public.contractprogress_id_seq') into v_billid;
	 insert into public.contractprogress(id,userid,bill_date,contractsid,code, name,message,
	  sourcetype,sourceid,percent,org_id,updated_at,created_at,departmentid) 
	 select v_billid,p_userid ,now(),id ,public.gaincode('contractprogress',0,'code',now(),org_id,p_userid),name,'',
	  'contracts',id,0,org_id,now(),now(),departmentid  from  public.contracts where id=p_sourcebillid;

	   insert into public.contractactivity(userid,contractsid,message,updated_at,
	   created_at,sourcetype ,sourceid)
	   select p_userid,p_sourcebillid,'合同违约登记',now(),
	   now(),p_bill ,v_billid from public.contractprogress where id=v_billid;

             select array_to_json(array_agg(row_to_json(t))) into v_data from (select 0 as flag,id,code from public.contractprogress where id=v_billid) t;
           return v_data::json;
    elsif p_bill='contractstop' then---合同终止申请单
        select count(*) into v_cnt
        from (select id From contractstop where contractsid=p_sourcebillid
        union 
       select id From contractfinish where contractsid=p_sourcebillid) t1;
       if v_cnt=0 then
	 select nextval('public.contractstop_id_seq') into v_billid;
	 insert into public.contractstop(id,userid,bill_date,contractsid,code, name,message,
	  sourcetype,sourceid,org_id,updated_at,created_at,departmentid) 
	 select v_billid,p_userid ,now(),id ,public.gaincode('contractstop',0,'code',now(),org_id,p_userid) ,name,'',
	  'contracts',id,org_id,now(),now(),departmentid  from  public.contracts where id=p_sourcebillid;
	  
	   insert into public.contractactivity(userid,contractsid,message,updated_at,
	   created_at,sourcetype ,sourceid)
	   select p_userid,p_sourcebillid,'合同终止',now(),
	   now(),p_bill ,v_billid from public.contractstop where id=v_billid;

           select array_to_json(array_agg(row_to_json(t))) into v_data from (select 0 as flag,id,code from public.contractstop where id=v_billid) t;
          return v_data::json;
        else
          select array_to_json(array_agg(row_to_json(t))) into v_data from 
          (select 1 as flag,1 as id,'合同终止申请单已存在:' || code as code  From contractstop where contractsid=p_sourcebillid
           union select 1 as flag,1 as id,'合同验收申请单已存在:' || code as code  From contractfinish where contractsid=p_sourcebillid) t;
          return v_data::json;  
        end if; 
    elsif p_bill='contractfinish' then---合同验收申请单
        select count(*) into v_cnt
        from (select id From contractstop where contractsid=p_sourcebillid
        union 
       select id From contractfinish where contractsid=p_sourcebillid) t1;
       if v_cnt=0 then
	 select nextval('public.contractfinish_id_seq') into v_billid;
	 insert into public.contractfinish(id,userid,bill_date,contractsid,code, name,message,
	  sourcetype,sourceid,org_id,updated_at,created_at,departmentid) 
	 select v_billid,p_userid ,now(),id ,public.gaincode('contractfinish',0,'code',now(),org_id,p_userid) ,name,'',
	  'contracts',id,org_id,now(),now(),departmentid  from  public.contracts where id=p_sourcebillid;

	   insert into public.contractactivity(userid,contractsid,message,updated_at,
	   created_at,sourcetype ,sourceid)
	   select p_userid,p_sourcebillid,'合同验收',now(),
	   now(),p_bill ,v_billid from public.contractfinish where id=v_billid;

            select array_to_json(array_agg(row_to_json(t))) into v_data from (select 0 as flag,id,code from public.contractfinish where id=v_billid) t;
           return v_data::json;
        else
          select array_to_json(array_agg(row_to_json(t))) into v_data from 
          (select 1 as flag,1 as id,'合同终止申请单已存在:' || code as code  From contractstop where contractsid=p_sourcebillid
           union select 1 as flag,1 as id,'合同验收申请单已存在:' || code as code  From contractfinish where contractsid=p_sourcebillid) t;
          return v_data::json;  
        end if; 
    elsif p_bill='contractsettle' then---合同付款单
	 select nextval('public.contractsettle_id_seq') into v_billid;
	 insert into public.contractsettle(id,userid,bill_date,contractsid,code, name,message,
	  sourcetype,sourceid,percent,org_id,updated_at,created_at,departmentid) 
	 select v_billid,p_userid ,now(),id ,public.gaincode('contractsettle',0,'code',now(),org_id,p_userid) ,name,'',
	  'contracts',id,0,org_id,now(),now(),departmentid  from  public.contracts where id=p_sourcebillid;

	   insert into public.contractactivity(userid,contractsid,message,updated_at,
	   created_at,sourcetype ,sourceid)
	   select p_userid,p_sourcebillid,'合同付款',now(),
	   now(),p_bill ,v_billid from public.contractsettle where id=v_billid;

          select array_to_json(array_agg(row_to_json(t))) into v_data from (select 0 as flag,id,code from public.contractsettle where id=v_billid) t;
         return v_data::json;
    end if;
  end if;
  select array_to_json(array_agg(row_to_json(t))) into v_data from (select 1 as flag,0 as id,'没有找到相关单据' code) t;
  return v_data::json;
END;  
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
